package com.testingedu.jdbcDao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.testingedu.common.QueryResult;
import com.testingedu.model.TOperateInRole;
import com.testingedu.model.TUser;

public class TOperateInRoleDao extends BaseDao<TOperateInRole> {
	
	public TOperateInRoleDao() {
		clazz = TOperateInRole.class;
	}

	public void list(QueryResult queryResult, TUser tUser) throws Exception{
		StringBuffer sql = new StringBuffer(
				"select * from t_operate tp where 1=1");
		List<Object> values = new ArrayList<Object>();
		if (tUser != null) {
			if (tUser.getName() != null && tUser.getName().trim().length() > 0) {
				sql.append(" and tp.name = ?");
				values.add(tUser.getName());
			}
		}
		try {
			this.myJdbcSetValuesAndExecuteQuery(sql.toString(), values);
			queryResult.setResultList(_resultSetToList(rs));
		} finally {
			close();
		}
		sql.replace(7, 8, "count(*)");
		try {
			this.myJdbcSetValuesAndExecuteQuery(sql.toString(), values);
			rs.next();
			queryResult.setAllCount(new Long(rs.getInt(1)));
		} catch (SQLException e) {
			e.printStackTrace();
			throw new Exception();
		} finally {
			close();
		}
	}
	
	public void add(Integer roleId, List<Integer> operateIds) throws Exception{
		StringBuffer sql = new StringBuffer("insert into t_operate_in_role(operate_id,role_id) values");
		List<Object> values = new ArrayList<Object>();
		try {
			for (int i = 0; i < operateIds.size(); i++) {
				sql.append(" (?,?)");
				values.add(operateIds.get(i));
				values.add(roleId);
				if(operateIds.size() > 1 && i < (operateIds.size() - 1)) {
					sql.append(",");
				}
			}
			this.myJdbcSetValuesAndExecuteSaveOrUpdateOrDelete(sql.toString(), values);
		} catch (Exception e) {
			e.printStackTrace();
			throw new Exception();
		}
		finally {
			this.close();
		}
		List<Integer> menuIds = new ArrayList<Integer>();
		String s = "select too.menu_id from t_operate too where too.id = ?";
		TMenuInRoleDao tMenuInRoleDao = new TMenuInRoleDao();
		for (Integer integer : operateIds) {
			values.clear();
			values.add(integer);
			try {
				this.myJdbcSetValuesAndExecuteQuery(s, values);
				if(rs.next()) {
					Integer menuId = rs.getInt("menu_id");
					if(tMenuInRoleDao.hasAdd(roleId, menuId)) {
						continue;
					}
					if(!menuIds.contains(menuId)) {
						menuIds.add(menuId);
					}
				}
			} catch (SQLException e) {
				e.printStackTrace();
				throw new Exception();
			}
			finally {
				this.close();
			}
		}
		if(menuIds.size() > 0) {
			tMenuInRoleDao.add(roleId, menuIds);
		}
	}
	
	public void delete(Integer roleId, List<Integer> operateIds) throws Exception{
		String sql = null;
		List<Object> values = new ArrayList<Object>();
		List<Integer> menuIds = new ArrayList<Integer>();
		for (Integer integer : operateIds) {
			values.clear();
			sql = "select tm.id from t_menu tm where tm.id in (select distinct(too.menu_id) from t_operate too where too.id = ? and too.id in (select toir.operate_id from t_operate_in_role toir where toir.role_id = ?))";
			values.add(integer);
			values.add(roleId);
			try {
				this.myJdbcSetValuesAndExecuteQuery(sql, values);
				if(rs.next()) {
					Integer id = rs.getInt("id");
					if(!menuIds.contains(id)) {
						menuIds.add(id);
					}
				}
			} catch (SQLException e) {
				e.printStackTrace();
				throw new Exception();
			}
			finally {
				this.close();
			}
		}
		for (int i = 0; i < operateIds.size(); i++) {
			sql = "delete from t_operate_in_role where role_id = ? and operate_id = ?";
			values.clear();
			values.add(roleId);
			values.add(operateIds.get(i));
			try {
				this.myJdbcSetValuesAndExecuteSaveOrUpdateOrDelete(sql, values);
			} catch (Exception e) {
				e.printStackTrace();
				throw new Exception();
			}
			finally {
				this.close();
			}
		}
		List<Integer> deleteIds = new ArrayList<Integer>();
		for(Integer integer : menuIds) {
			sql = "select too.menu_id from t_operate too where too.menu_id = ? and too.id in (select toir.operate_id from t_operate_in_role toir where toir.role_id = ?)";
			values.clear();
			values.add(integer);
			values.add(roleId);
			try {
				this.myJdbcSetValuesAndExecuteQuery(sql, values);
				if(!rs.next()) {
					deleteIds.add(integer);
				}
			} catch (SQLException e) {
				e.printStackTrace();
				throw new Exception();
			}
			finally {
				this.close();
			}
		}
		new TMenuInRoleDao().delete(roleId, deleteIds);
	}
	
	public void add(TUser tUser) throws Exception{
		String sql = "insert into t_operate(account,password,name,photo_path,description,certificate_id,certificate_name) values(?,?,?,?,?,?,?)";
		List<Object> values = new ArrayList<Object>();
		values.add(tUser.getAccount());
		values.add(tUser.getPassword());
		values.add(tUser.getName());
		values.add(tUser.getPhotoPath());
		values.add(tUser.getDescription());
		values.add(tUser.getCertificateId());
		values.add(tUser.getCertificateName());
		try {
			this.myJdbcSetValuesAndExecuteSaveOrUpdateOrDelete(sql, values);
		} catch (Exception e) {
			e.printStackTrace();
			throw new Exception();
		} finally {
			close();
		}
	}

	public void update(TUser tUser) throws Exception{
		String sql = "update t_operate set name = ?, description = ? where id=?";
		List<Object> values = new ArrayList<Object>();
		values.add(tUser.getName());
		values.add(tUser.getDescription());
		values.add(tUser.getId());
		try {
			this.myJdbcSetValuesAndExecuteSaveOrUpdateOrDelete(sql, values);
		} catch (Exception e) {
			e.printStackTrace();
			throw new Exception();
		} finally {
			close();
		}
	}

	public void delete(int id) throws Exception{
		String sql = "delete from t_operate tp where tp.id = ?";
		try {
			this.myJdbcSetValuesAndExecuteDelete(sql, id);
		} catch (Exception e) {
			e.printStackTrace();
			throw new Exception();
		} finally {
			close();
		}
	}

	public TUser info(int id) throws Exception{
		String sql = "select * from t_operate tp where tp.id = ?";
		List<TUser> result = null;
		try {
			this.myJdbcSetValuesAndExecuteLoad(sql, id);
			result = _resultSetToList(rs);
		} catch (Exception e) {
			e.printStackTrace();
			throw new Exception();
		} finally {
			close();
		}
		if (result != null && result.size() > 0) {
			return result.get(0);
		}
		return null;
	}

	public boolean validatePermission(String url, Integer userId) throws Exception{
		String sql = "select too.id from t_operate too where too.url = ? and too.id in (select toir.operate_id from t_operate_in_role toir where toir.role_id in (select triu.role_id from t_role_in_user triu where "
				+ "triu.user_id = ?))";
		List<Object> values = new ArrayList<Object>();
		values.add(url);
		values.add(userId);
		try {
			this.myJdbcSetValuesAndExecuteQuery(sql, values);
			if(rs.next()) {
				return true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new Exception();
		}
		finally {
			close();
		}
		return false;
	}

	public List<TUser> _resultSetToList(ResultSet rs) throws Exception{
		List<TUser> result = new ArrayList<TUser>();
		try {
			while (rs.next()) {
				TUser tUser = new TUser();
				tUser.setId(rs.getInt("id"));
				tUser.setAccount(rs.getString("account"));
				tUser.setName(rs.getString("name"));
				tUser.setPhotoPath(rs.getString("photo_path"));
				tUser.setDescription(rs.getString("description"));
				result.add(tUser);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new Exception();
		}
		return result;
	}

}